
package net.swa.dewei.service.impl;

import net.swa.dewei.beans.entity.Battery;
import net.swa.dewei.beans.entity.BatteryCheXingRelation;
import net.swa.dewei.beans.entity.BatteryPrice;
import net.swa.dewei.beans.vo.VoBattery;
import net.swa.dewei.beans.vo.VoBatteryPrice;
import net.swa.dewei.service.BatteryService;
import net.swa.system.dao.HibernateDaoSupport;
import net.swa.util.BuzConst;
import net.swa.util.DateUtils;
import net.swa.util.JsonResult;
import net.swa.util.io.ExcelUtil;
import net.swa.util.io.enums.MsOfficeVersion;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Service;

@Service("batteryService")
public class BatteryServiceImpl extends HibernateDaoSupport implements BatteryService
{
    private final Logger log = Logger.getLogger(BatteryServiceImpl.class);

    public boolean add(final Battery m , final Long[] ids)
    {
        //校验该品牌型号电池是否已经存在
        String sql = "select  count(1) as counNum from   t_Battery b  where b.pinPai=:pinPai and b.xingHao=:xingHao";
        Query query = getCurrentSession().createSQLQuery(sql).addScalar("counNum", StandardBasicTypes.INTEGER);
        query.setString("pinPai", m.getPinPai());
        query.setString("xingHao", m.getXingHao());
        Integer totalCount = (Integer) query.uniqueResult();
        if (null != totalCount)
        {
            if (totalCount > 0)
            {
                return false;
            }
        }
        getCurrentSession().save(m);
        if (ids.length > 0)
        {
            for (int i = 0; i < ids.length; i++)
            {
                BatteryCheXingRelation r = new BatteryCheXingRelation();
                r.setDianchiId(m.getId());
                r.setChexingId(ids[i]);
                getCurrentSession().save(r);
            }
        }
        return true;
    }

    public boolean update(final Battery m , final Long[] ids)
    {
        //校验该品牌型号电池是否已经存在(不包括本条记录)
        String sql = "select  count(1) as counNum from   t_Battery b  where b.pinPai=:pinPai and b.xingHao=:xingHao and b.id<>:id";
        Query query = getCurrentSession().createSQLQuery(sql).addScalar("counNum", StandardBasicTypes.INTEGER);
        query.setString("pinPai", m.getPinPai());
        query.setString("xingHao", m.getXingHao());
        query.setLong("id", m.getId());
        Integer totalCount = (Integer) query.uniqueResult();
        if (null != totalCount)
        {
            if (totalCount > 0)
            {
                return false;
            }
        }

        query = getCurrentSession().createQuery("delete from BatteryCheXingRelation where dianchiId=:id");
        query.setLong("id", m.getId());
        query.executeUpdate();
        getCurrentSession().update(m);
        if (ids.length > 0)
        {
            for (int i = 0; i < ids.length; i++)
            {
                BatteryCheXingRelation r = new BatteryCheXingRelation();
                r.setDianchiId(m.getId());
                r.setChexingId(ids[i]);
                getCurrentSession().save(r);
            }
        }
        return true;
    }

    public boolean delete(final Long[] ids)
    {
        Query query = null;
        for (int i = 0; i < ids.length; i++)
        {
            query = getCurrentSession().createQuery("delete from BatteryCheXingRelation where dianchiId=:id");
            query.setLong("id", ids[i]);
            query.executeUpdate();
            query = getCurrentSession().createQuery("delete from Battery where id=:id");
            query.setLong("id", ids[i]);
            query.executeUpdate();
            query = getCurrentSession().createQuery("delete from BatteryPrice where batteryId=:id");
            query.setLong("id", ids[i]);
            query.executeUpdate();
        }
        return true;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Battery queryById(final Long id)
    {
        Battery m = (Battery) getCurrentSession().get(Battery.class, id);
        Query query = getCurrentSession().createQuery("from BatteryCheXingRelation where dianchiId=:id");
        query.setLong("id", id);
        List<BatteryCheXingRelation> l = query.list();
        m.setRelationList(l);
        return m;
    }

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<Battery> queryDianchiByCheXingId(final Long id)
    {
        // 默认查询最大数量
        final int count = 10;
        Query query = null;
        String hql = "from BatteryCheXingRelation where 1=1 ";
        if (null != id)
        {
            hql += " and chexingId=:id";
        }
        query = getCurrentSession().createQuery(hql);
        if (null != id)
        {
            query.setLong("id", id);
        }
        List<BatteryCheXingRelation> l = query.list();
        hql = "from DianChi where 1=1 ";
        if (null != l && l.size() > 0)
        {
            String dianchiIds = "";
            for (int i = 0; i < l.size() - 1; i++)
            {

                dianchiIds = dianchiIds + l.get(i).getDianchiId() + ",";
            }
            dianchiIds = dianchiIds + l.get(l.size() - 1).getDianchiId();
            hql += " and id in(" + dianchiIds + ") ";
        }
        hql += " order by xingHao,pinPai,sort";
        query = getCurrentSession().createQuery(hql);
        // 最多查询记录数
        query.setMaxResults(count);
        List<Battery> rlist = query.list();
        JsonResult<Battery> json = new JsonResult<Battery>();
        json.setResult(rlist);
        json.setSuccess(true);
        return json;
    }

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<Battery> queryDianchis(final Long id , final String province , final String city , final String town)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town);
        }
        //默认查询最大数量
        final int count = 20;
        Query query = null;
        JsonResult<Battery> json = new JsonResult<Battery>();
        StringBuilder sb = new StringBuilder();
        //车型对应的电池
        List<Battery> rlist1 = null;
        //单独定价信息
        List<Battery> rlist2 = null;

        //查询该省市县区指定车型的电池 --start
        //该段代码暂时屏蔽，查询为指定型号的所有电池（如需更改只用这段代码）
        //        sb = new StringBuilder();
        //        sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,p.imageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort from  t_Battery t ");
        //        sb.append("join t_Battery_CheXing r on r.dianchiId=t.id left join t_Battery_Price p on t.id=p.batteryId  where 1=1 ");
        //        if (null != id)
        //        {
        //            sb.append(" and r.chexingId=:id");
        //        }
        //        if (!StringUtils.isBlank(province))
        //        {
        //            if (province.length() > 4)
        //            {
        //                sb.append(" and p.province  like :province");
        //            }
        //            else
        //            {
        //                sb.append(" and p.province=:province");
        //            }
        //        }
        //        if (!StringUtils.isBlank(city))
        //        {
        //            sb.append(" and p.city=:city");
        //        }
        //        if (!StringUtils.isBlank(town))
        //        {
        //            if (town.length() <= 4)
        //            {
        //                sb.append(" and p.town=:town");
        //            }
        //            else
        //            {
        //                sb.append(" and p.town like :town");
        //            }
        //        }
        //        sb.append(" order by sort");
        //        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao",
        //                StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate",
        //                StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));
        //        if (null != id)
        //        {
        //            query.setLong("id", id);
        //        }
        //        if (!StringUtils.isBlank(province))
        //        {
        //            if (province.length() > 4)
        //            {
        //                query.setString("province", province.substring(0, 2) + "%");
        //            }
        //            else
        //            {
        //                query.setString("province", province);
        //            }
        //        }
        //        if (!StringUtils.isBlank(city))
        //        {
        //            query.setString("city", city);
        //        }
        //        if (!StringUtils.isBlank(town))
        //        {
        //            if (town.length() <= 4)
        //            {
        //                query.setString("town", town);
        //            }
        //            else
        //            {
        //                query.setString("town", "%" + town.substring(0, town.length() - 1) + "%");
        //            }
        //        }
        //        query.setMaxResults(count);
        //        rlist2 = query.list();
        //        if (null != rlist2 && rlist2.size() > 0)
        //        {
        //            json.setResult(rlist2);
        //            json.setSuccess(true);
        //        }
        //查询该省市县区指定车型的电池 --end

        sb = new StringBuilder();
        sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,t.minImageUrl,t.marketPrice,t.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort from  t_Battery t ").append("join t_Battery_CheXing r on r.dianchiId=t.id  where 1=1 ");
        if (null != id)
        {
            sb.append(" and r.chexingId=:id");
        }
        sb.append(" order by sort");
        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar("marketPrice",
                StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));
        if (null != id)
        {
            query.setLong("id", id);
        }
        //最多查询记录数
        query.setMaxResults(count);
        rlist1 = query.list();
        if (!((StringUtils.isBlank(province)) && (StringUtils.isBlank(province)) && (StringUtils.isBlank(province))))
        {
            sb = new StringBuilder();
            sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,p.imageUrl,p.minImageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort from  t_Battery t ");
            sb.append("join t_Battery_CheXing r on r.dianchiId=t.id left join t_Battery_Price p on t.id=p.batteryId  where 1=1 ");
            if (null != id)
            {
                sb.append(" and r.chexingId=:id");
            }
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 4)
                {
                    sb.append(" and p.province  like :province");
                }
                else
                {
                    sb.append(" and p.province=:province");
                }
            }
            if (!StringUtils.isBlank(city))
            {
                sb.append(" and p.city=:city");
            }
            if (!StringUtils.isBlank(town))
            {
                if (town.length() <= 4)
                {
                    sb.append(" and p.town=:town");
                }
                else
                {
                    sb.append(" and p.town like :town");
                }

            }
            sb.append(" order by sort");
            query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar(
                    "marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));

            if (null != id)
            {
                query.setLong("id", id);
            }
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 4)
                {
                    query.setString("province", province.substring(0, 2) + "%");
                }
                else
                {
                    query.setString("province", province);
                }
            }
            if (!StringUtils.isBlank(city))
            {
                query.setString("city", city);
            }
            if (!StringUtils.isBlank(town))
            {
                if (town.length() <= 4)
                {
                    query.setString("town", town);
                }
                else
                {
                    query.setString("town", "%" + town.substring(0, town.length() - 1) + "%");
                }
            }
            //最多查询记录数
            query.setMaxResults(count);
            rlist2 = query.list();
            if (null != rlist2 && rlist2.size() > 0)
            {
                for (Battery b2 : rlist2)
                {
                    for (Battery b1 : rlist1)
                    {
                        if (b2.getId() == b1.getId())
                        {
                            if (!StringUtils.isBlank(b2.getImageUrl()))
                            {
                                b1.setImageUrl(b2.getImageUrl());
                            }
                            if (!StringUtils.isBlank(b2.getMinImageUrl()))
                            {
                                b1.setMinImageUrl(b2.getMinImageUrl());
                            }
                            if (!StringUtils.isBlank(b2.getOurPrice()))
                            {
                                b1.setOurPrice(b2.getOurPrice());
                            }
                            if (!StringUtils.isBlank(b2.getMarketPrice()))
                            {
                                b1.setMarketPrice(b2.getMarketPrice());
                            }
                        }
                    }
                }
            }
        }
        json.setResult(rlist1);
        json.setSuccess(true);
        return json;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Battery> queryBettys(final Long id , final String province , final String city , final String town)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town);
        }
        //默认查询最大数量
        final int count = 2000;
        Query query = null;
        StringBuilder sb = new StringBuilder();
        List<Battery> result = new ArrayList<Battery>();
        //车型对应的电池
        List<Battery> rlist1 = null;
        //单独定价信息
        List<Battery> rlist2 = null;

        //查询该省市县区指定车型的电池 --start
        //该段代码暂时屏蔽，查询为指定型号的所有电池（如需更改只用这段代码）
        //        sb = new StringBuilder();
        //        sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,p.imageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort from  t_Battery t ");
        //        sb.append("join t_Battery_CheXing r on r.dianchiId=t.id left join t_Battery_Price p on t.id=p.batteryId  where 1=1 ");
        //        if (null != id)
        //        {
        //            sb.append(" and r.chexingId=:id");
        //        }
        //        if (!StringUtils.isBlank(province))
        //        {
        //            if (province.length() > 4)
        //            {
        //                sb.append(" and p.province  like :province");
        //            }
        //            else
        //            {
        //                sb.append(" and p.province=:province");
        //            }
        //        }
        //        if (!StringUtils.isBlank(city))
        //        {
        //            sb.append(" and p.city=:city");
        //        }
        //        if (!StringUtils.isBlank(town))
        //        {
        //            if (town.length() <= 4)
        //            {
        //                sb.append(" and p.town=:town");
        //            }
        //            else
        //            {
        //                sb.append(" and p.town like :town");
        //            }
        //        }
        //        sb.append(" order by sort");
        //        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao",
        //                StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate",
        //                StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));
        //        if (null != id)
        //        {
        //            query.setLong("id", id);
        //        }
        //        if (!StringUtils.isBlank(province))
        //        {
        //            if (province.length() > 4)
        //            {
        //                query.setString("province", province.substring(0, 2) + "%");
        //            }
        //            else
        //            {
        //                query.setString("province", province);
        //            }
        //        }
        //        if (!StringUtils.isBlank(city))
        //        {
        //            query.setString("city", city);
        //        }
        //        if (!StringUtils.isBlank(town))
        //        {
        //            if (town.length() <= 4)
        //            {
        //                query.setString("town", town);
        //            }
        //            else
        //            {
        //                query.setString("town", "%" + town.substring(0, town.length() - 1) + "%");
        //            }
        //        }
        //        query.setMaxResults(count);
        //        rlist2 = query.list();
        //        if (null != rlist2 && rlist2.size() > 0)
        //        {
        //            json.setResult(rlist2);
        //            json.setSuccess(true);
        //        }
        //查询该省市县区指定车型的电池 --end

        sb = new StringBuilder();
        sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,t.minImageUrl,t.marketPrice,t.ourPrice,t.detailInfo,t.crateDate,t.updateDate,t.state, t.sort+1000 as sort from  t_Battery t ").append("join t_Battery_CheXing r on r.dianchiId=t.id  where 1=1 ");
        if (null != id)
        {
            sb.append(" and r.chexingId=:id");
        }
        sb.append(" order by t.sort");
        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar("marketPrice",
                StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("state", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(Battery.class));
        if (null != id)
        {
            query.setLong("id", id);
        }
        //最多查询记录数
        query.setMaxResults(count);
        rlist1 = query.list();
        if (!((StringUtils.isBlank(province)) && (StringUtils.isBlank(province)) && (StringUtils.isBlank(province))))
        {
            sb = new StringBuilder();
            sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,p.imageUrl,p.minImageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, p.sort from  t_Battery t ");
            sb.append("join t_Battery_CheXing r on r.dianchiId=t.id left join t_Battery_Price p on t.id=p.batteryId  where 1=1 ");
            if (null != id)
            {
                sb.append(" and r.chexingId=:id");
            }
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 3)
                {
                    sb.append(" and p.province  like :province");
                }
                else
                {
                    sb.append(" and p.province  like :province");
                }
            }
            if (!StringUtils.isBlank(city))
            {
                sb.append(" and p.city like :city");
            }
            if (!StringUtils.isBlank(town))
            {
                if (town.length() <= 4)
                {
                    sb.append(" and p.town like :town");
                }
                else
                {
                    sb.append(" and p.town like :town");
                }
            }
            sb.append(" order by sort");
            query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar(
                    "marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));
            if (null != id)
            {
                query.setLong("id", id);
            }
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 4)
                {
                    query.setString("province", province.substring(0, 2) + "%");
                }
                else
                {
                    query.setString("province", province + "%");
                }
            }
            if (!StringUtils.isBlank(city))
            {
                query.setString("city", city + "%");
            }
            if (!StringUtils.isBlank(town))
            {
                if (town.length() <= 4)
                {
                    query.setString("town", town.substring(0, town.length() - 1) + "%");
                }
                else
                {
                    query.setString("town", "%" + town.substring(0, town.length() - 1) + "%");
                }
            }
            //最多查询记录数
            query.setMaxResults(count);
            rlist2 = query.list();
            if (null != rlist2 && rlist2.size() > 0)
            {
                for (Battery b2 : rlist2)
                {
                    for (Battery b1 : rlist1)
                    {
                        if (b2.getId() == b1.getId())
                        {
                            if (!StringUtils.isBlank(b2.getImageUrl()))
                            {
                                b1.setImageUrl(b2.getImageUrl());
                            }
                            if (!StringUtils.isBlank(b2.getMinImageUrl()))
                            {
                                b1.setMinImageUrl(b2.getMinImageUrl());
                            }
                            if (!StringUtils.isBlank(b2.getOurPrice()))
                            {
                                b1.setOurPrice(b2.getOurPrice());
                            }
                            if (!StringUtils.isBlank(b2.getMarketPrice()))
                            {
                                b1.setMarketPrice(b2.getMarketPrice());
                            }
                            if (null != b2.getSort())
                            {
                                b1.setSort(b2.getSort());
                            }
                            b1.setState(BuzConst.CHEXING_STATE_SHOW);
                        }
                    }
                }
            }
        }
        if (null != rlist1 && rlist1.size() > 0)
        {
            for (Battery o : rlist1)
            {
                if (null != o.getState())
                {
                    if (o.getState().equals(BuzConst.CHEXING_STATE_SHOW))
                    {
                        result.add(o);
                    }
                }
            }
            Collections.sort(result);
        }
        return result;
    }

    /****
     * 电池价格分页信息查询
     * 关联电池基本表sql join
     */
    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<BatteryPrice> queryPricePage(final String province , final String city , final String town , final String pinPai , final String xingHao , final int currentPage , final int pageSize , final String orderBy , final String orderType)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town + ",pinPai is " + pinPai + ",xingHao is " + xingHao);
        }
        JsonResult<BatteryPrice> json = new JsonResult<BatteryPrice>();

        StringBuilder sql = new StringBuilder();
        StringBuilder countsql = new StringBuilder();
        Query query = null;
        countsql.append("select count(1) as counNum  from  t_Battery_Price p join t_Battery b on p.batteryId=b.id  where 1=1 ");
        sql.append("select p.id, b.pinPai, b.xingHao, p.batteryId, b.imageUrl, p.marketPrice,  p.ourPrice,p.sort,p.province,p.city,p.town,p.updateDate from  t_Battery_Price p join t_Battery b on p.batteryId=b.id  where 1=1 ");

        if (!StringUtils.isBlank(province))
        {
            sql.append(" and p.province =:province");
            countsql.append(" and p.province =:province");
        }
        if (!StringUtils.isBlank(city))
        {
            sql.append(" and p.city =:city");
            countsql.append(" and p.city =:city");
        }
        if (!StringUtils.isBlank(town))
        {
            sql.append(" and p.town=:town");
            countsql.append(" and p.town=:town");
        }
        if (!StringUtils.isBlank(pinPai))
        {
            sql.append(" and b.pinPai =:pinPai");
            countsql.append(" and b.pinPai =:pinPai");
        }
        if (!StringUtils.isBlank(xingHao))
        {
            sql.append(" and b.xingHao=:xingHao");
            countsql.append(" and b.xingHao=:xingHao");
        }

        if (!StringUtils.isBlank(orderBy))
        {
            sql.append("  order by " + orderBy);
            if (!StringUtils.isBlank(orderType))
            {
                sql.append("  " + orderType);
            }
        }

        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("batteryId", StandardBasicTypes.LONG).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("province", StandardBasicTypes.STRING).addScalar("city",
                StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(BatteryPrice.class));

        if (!StringUtils.isBlank(province))
        {
            query.setString("province", province);
        }
        if (!StringUtils.isBlank(city))
        {
            query.setString("city", city);
        }
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        if (!StringUtils.isBlank(pinPai))
        {
            query.setString("pinPai", pinPai);
        }
        if (!StringUtils.isBlank(xingHao))
        {
            query.setString("xingHao", xingHao);
        }
        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            query.setFirstResult(from);
            query.setMaxResults(pageSize);
        }
        List<BatteryPrice> list = query.list();

        query = getCurrentSession().createSQLQuery(countsql.toString()).addScalar("counNum", StandardBasicTypes.INTEGER);
        if (!StringUtils.isBlank(province))
        {
            query.setString("province", province);
        }
        if (!StringUtils.isBlank(city))
        {
            query.setString("city", city);
        }
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        if (!StringUtils.isBlank(pinPai))
        {
            query.setString("pinPai", pinPai);
        }
        if (!StringUtils.isBlank(xingHao))
        {
            query.setString("xingHao", xingHao);
        }
        if (pageSize > 0)
        {
            int totalCount = (Integer) query.uniqueResult();
            int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
            json.setTotalSize(totalCount);
            json.setTotalPage(totalPage);
            json.setPageSize(pageSize);
            json.setCurrentPage(currentPage);
        }
        json.setResult(list);
        return json;
    }

    @SuppressWarnings("unchecked")
    @Override
    public BatteryPrice queryPriceById(final Long id)
    {
        String sql = "select p.id, b.pinPai, b.xingHao, p.batteryId, b.imageUrl, p.marketPrice,  p.ourPrice,p.sort,p.province,p.city,p.town  from  t_Battery_Price p join t_Battery b on p.batteryId=b.id  where p.id=:id ";
        Query query = getCurrentSession().createSQLQuery(sql).addScalar("id", StandardBasicTypes.LONG).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("batteryId", StandardBasicTypes.LONG).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("province", StandardBasicTypes.STRING).addScalar("city",
                StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(BatteryPrice.class));
        query.setLong("id", id);
        List<BatteryPrice> list = query.list();
        if (null != list && list.size() > 0)
        {
            BatteryPrice m = list.get(0);
            if (null == m.getSort())
            {
                m.setSort(1);
            }
            return m;
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<Object> saveBatteryPrice(BatteryPrice model)
    {
        JsonResult<Object> json = new JsonResult<Object>();
        //检测相同地区相同品牌型号是否存在
        String sql = "select  count(1) as counNum from   t_Battery_Price p join t_Battery b on p.batteryId=b.id where p.province=:province and p.city=:city and  p.town=:town and b.pinPai=:pinPai and b.xingHao=:xingHao ";
        Query query = getCurrentSession().createSQLQuery(sql).addScalar("counNum", StandardBasicTypes.INTEGER);
        query.setString("province", model.getProvince());
        query.setString("city", model.getCity());
        query.setString("town", model.getTown());
        query.setString("pinPai", model.getPinPai());
        query.setString("xingHao", model.getXingHao());
        Integer totalCount = (Integer) query.uniqueResult();
        if (null != totalCount)
        {
            if (totalCount > 0)
            {
                json.setSuccess(false);
                json.setMessage("已经存在该地区该种电池价格");
                return json;
            }
        }
        query = getCurrentSession().createQuery("from Battery where xingHao=:xingHao and pinPai=:pinPai");
        query.setString("pinPai", model.getPinPai());
        query.setString("xingHao", model.getXingHao());
        List<Battery> list = query.list();
        if (null != list && list.size() > 0)
        {
            Battery b = list.get(0);
            model.setBatteryId(b.getId());
            Serializable r = getCurrentSession().save(model);
            if (log.isDebugEnabled())
            {
                log.debug("保存电池价格，操作结果：" + r);
            }
            json.setSuccess(true);
        }
        else
        {
            json.setSuccess(false);
            json.setMessage("该电池类型不存在");
        }
        return json;
    }

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<Object> updateBatteryPrice(BatteryPrice model)
    {
        JsonResult<Object> json = new JsonResult<Object>();
        json.setSuccess(false);
        json.setMessage("处理失败");
        //检测相同地区相同品牌型号是否存在(除了本条记录)
        String sql = "select  count(1) as counNum from   t_Battery_Price p join t_Battery b on p.batteryId=b.id where p.province=:province and p.city=:city and  p.town=:town and b.pinPai=:pinPai and b.xingHao=:xingHao and p.id<>:id ";
        Query query = getCurrentSession().createSQLQuery(sql).addScalar("counNum", StandardBasicTypes.INTEGER);
        query.setString("province", model.getProvince());
        query.setString("city", model.getCity());
        query.setString("town", model.getTown());
        query.setString("pinPai", model.getPinPai());
        query.setString("xingHao", model.getXingHao());
        query.setLong("id", model.getId());
        Integer totalCount = (Integer) query.uniqueResult();
        if (null != totalCount)
        {
            if (totalCount > 0)
            {
                json.setSuccess(false);
                json.setMessage("已经存在该地区该种电池价格");
                return json;
            }
        }
        query = getCurrentSession().createQuery("from Battery where xingHao=:xingHao and pinPai=:pinPai");
        query.setString("pinPai", model.getPinPai());
        query.setString("xingHao", model.getXingHao());
        List<Battery> list = query.list();
        if (null != list && list.size() > 0)
        {
            Battery b = list.get(0);
            model.setBatteryId(b.getId());
            getCurrentSession().update(model);
            json.setSuccess(true);
            json.setMessage("更新成功");
            if (log.isDebugEnabled())
            {
                log.debug("保存电池价格，操作结果：");
            }
            json.setSuccess(true);
        }
        else
        {
            json.setSuccess(false);
            json.setMessage("该电池类型不存在");
        }
        return json;
    }

    @Override
    public void deletePrices(Long[] ids)
    {
        Query query = getCurrentSession().createQuery("delete from BatteryPrice where id=:id");
        if (null != ids && ids.length > 0)
        {
            for (Long id : ids)
            {
                query.setLong("id", id);
                query.executeUpdate();
            }
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<VoBattery> queryPageByPinpXinghChex(String province , String city , String town , String pinPai , String xingHao , Long cxid , int currentPage , int pageSize , String orderBy , String orderType)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town + ",品牌 is " + pinPai + ",xinghao is " + xingHao + ",车型id is" + cxid);
        }
        StringBuilder sb = new StringBuilder();
        StringBuilder countsql = new StringBuilder();
        JsonResult<VoBattery> json = new JsonResult<VoBattery>();
        Query query = null;
        if (null != cxid)
        {
            if (!StringUtils.isBlank(province))
            {
                countsql.append("select count(1) as counNum  ").append("from t_Battery t  join  t_Battery_Price p  on t.id=p.batteryId ").append("join t_Battery_CheXing r on r.dianchiId=t.id ").append("where r.chexingId=:cxid  ").append("and p.province=:province ");

                sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,").append("t.minImageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, p.sort,p.province,p.city,p.town ").append("from t_Battery t  join  t_Battery_Price p  on t.id=p.batteryId ").append("join t_Battery_CheXing r on r.dianchiId=t.id ").append("where r.chexingId=:cxid  ").append("and p.province=:province ");
                if (!StringUtils.isBlank(city))
                {
                    sb.append("and  p.city=:city ");
                    countsql.append("and  p.city=:city ");
                }
                if (!StringUtils.isBlank(town))
                {
                    sb.append("and  p.town=:town ");
                    countsql.append("and  p.town=:town ");
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    sb.append(" and t.pinPai =:pinPai");
                    countsql.append(" and t.pinPai =:pinPai");
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    sb.append(" and t.xingHao=:xingHao");
                    countsql.append(" and t.xingHao=:xingHao");
                }
            }
            else
            {
                countsql.append("select count(1) as counNum  ").append("from  t_Battery t ").append("join t_Battery_CheXing r on r.dianchiId=t.id ").append("where r.chexingId=:cxid  ");

                sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,").append("t.minImageUrl,t.marketPrice,t.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort,null as province,null as city, null as town ").append("from  t_Battery t ").append("join t_Battery_CheXing r on r.dianchiId=t.id ").append("where r.chexingId=:cxid  ");

                if (!StringUtils.isBlank(pinPai))
                {
                    sb.append(" and t.pinPai =:pinPai");
                    countsql.append(" and t.pinPai =:pinPai");
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    sb.append(" and t.xingHao=:xingHao");
                    countsql.append(" and t.xingHao=:xingHao");
                }
            }
        }
        else
        {
            //不存在车型
            if (!StringUtils.isBlank(province))
            {
                countsql.append("select count(1) as counNum  ").append("from t_Battery t  join  t_Battery_Price p  on t.id=p.batteryId ").append("where  p.province=:province ");

                sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,").append("t.minImageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, p.sort,p.province,p.city,p.town ").append("from t_Battery t  join  t_Battery_Price p  on t.id=p.batteryId ").append("where  p.province=:province ");
                if (!StringUtils.isBlank(city))
                {
                    sb.append("and  p.city=:city ");
                    countsql.append("and  p.city=:city ");
                }
                if (!StringUtils.isBlank(town))
                {
                    sb.append("and  p.town=:town ");
                    countsql.append("and  p.town=:town ");
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    sb.append(" and t.pinPai =:pinPai");
                    countsql.append(" and t.pinPai =:pinPai");
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    sb.append(" and t.xingHao=:xingHao");
                    countsql.append(" and t.xingHao=:xingHao");
                }
            }
            else
            {
                countsql.append("select count(1) as counNum  ").append("from  t_Battery t where 1=1 ");

                sb.append("select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,").append("t.minImageUrl,t.marketPrice,t.ourPrice,t.detailInfo,t.crateDate,t.updateDate, t.sort,null as province,null as city, null as town ").append("from  t_Battery t ").append("where 1=1  ");

                if (!StringUtils.isBlank(pinPai))
                {
                    sb.append(" and t.pinPai =:pinPai");
                    countsql.append(" and t.pinPai =:pinPai");
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    sb.append(" and t.xingHao=:xingHao");
                    countsql.append(" and t.xingHao=:xingHao");
                }
            }
        }

        if (!StringUtils.isBlank(orderBy))
        {
            sb.append("  order by " + orderBy);
            if (!StringUtils.isBlank(orderType))
            {
                sb.append("  " + orderType);
            }
        }

        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("province",
                StandardBasicTypes.STRING).addScalar("city", StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(VoBattery.class));
        if (null != cxid)
        {
            if (!StringUtils.isBlank(province))
            {

                query.setLong("cxid", cxid);
                query.setString("province", province);

                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
                if (!StringUtils.isBlank(town))
                {
                    query.setString("town", town);
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
            else
            {
                query.setLong("cxid", cxid);
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
        }
        else
        {
            //不存在车型
            if (!StringUtils.isBlank(province))
            {
                query.setString("province", province);
                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
                if (!StringUtils.isBlank(town))
                {
                    query.setString("town", town);
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
            else
            {
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
        }

        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            query.setFirstResult(from);
            query.setMaxResults(pageSize);
        }
        List<VoBattery> list = query.list();
        query = getCurrentSession().createSQLQuery(countsql.toString()).addScalar("counNum", StandardBasicTypes.INTEGER);
        if (null != cxid)
        {
            if (!StringUtils.isBlank(province))
            {

                query.setLong("cxid", cxid);
                query.setString("province", province);

                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
                if (!StringUtils.isBlank(town))
                {
                    query.setString("town", town);
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
            else
            {
                query.setLong("cxid", cxid);
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
        }
        else
        {
            //不存在车型
            if (!StringUtils.isBlank(province))
            {
                query.setString("province", province);
                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
                if (!StringUtils.isBlank(town))
                {
                    query.setString("town", town);
                }
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
            else
            {
                if (!StringUtils.isBlank(pinPai))
                {
                    query.setString("pinPai", pinPai);
                }
                if (!StringUtils.isBlank(xingHao))
                {
                    query.setString("xingHao", xingHao);
                }
            }
        }
        if (pageSize > 0)
        {
            int totalCount = (Integer) query.uniqueResult();
            int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
            json.setTotalSize(totalCount);
            json.setTotalPage(totalPage);
            json.setPageSize(pageSize);
            json.setCurrentPage(currentPage);
        }
        json.setResult(list);
        json.setSuccess(true);
        return json;
    }

    /****
     * 需要修改进一步实现
     */
    @SuppressWarnings("unchecked")
    @Override
    public JsonResult<VoBattery> queryPageByPinpXinghChex2(String province , String city , String town , String pinPai , String xingHao , Long cxid , int currentPage , int pageSize , String orderBy , String orderType)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town + ",品牌 is " + pinPai + ",xinghao is " + xingHao + ",车型id is" + cxid);
        }
        StringBuilder sb = new StringBuilder();
        StringBuilder countsql = new StringBuilder();
        JsonResult<VoBattery> json = new JsonResult<VoBattery>();
        Query query = null;
        //若地址有区县信息，结果为该区县能看到的所有电池信息
        if (!StringUtils.isBlank(town))
        {
            countsql.append("select count(1) as counNum  ").append("from (select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,p.imageUrl as imageUrlTrue,t.minImageUrl,t.marketPrice,p.marketPrice as marketPriceTrue,t.ourPrice,p.ourPrice as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,t.sort,p.sort as sortTrue,p.province,p.city,p.town ").append("from t_battery t  LEFT JOIN ").append(
                    "(select  id, imageUrl,marketPrice,ourPrice,sort,province,city,town,batteryId from t_battery_price  ").append(" where town=:town  and city=:city and province=:province  ").append(") p ON p.batteryId=t.id ").append(") as result ");

            sb.append("select result.id, result.name, result.zhongliang,result.licheng,result.pinPai,result.xingHao,result.subName,result.imageUrl,result.imageUrlTrue,result.minImageUrl,result.marketPrice,result.marketPriceTrue,result.ourPrice,result.ourPriceTrue,result.detailInfo,result.crateDate,result.updateDate,IFNULL(result.sortTrue,result.sort+10000) as sort,province,city,town ").append(
                    "from (select t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,p.imageUrl as imageUrlTrue,t.minImageUrl,t.marketPrice,p.marketPrice as marketPriceTrue,t.ourPrice,p.ourPrice as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,t.sort,p.sort as sortTrue,p.province,p.city,p.town ").append("from t_battery t  LEFT JOIN ").append("(select  id, imageUrl,marketPrice,ourPrice,sort,province,city,town,batteryId from t_battery_price  ").append(
                    " where town=:town and city=:city and province=:province ").append(") p ON p.batteryId=t.id ").append(") as result  ");
            if (null != cxid)
            {
                countsql.append("join t_Battery_CheXing r on r.dianchiId=result.id ").append(" where  r.chexingId=:cxid ");
                sb.append("join t_Battery_CheXing r on r.dianchiId=result.id ").append(" where r.chexingId=:cxid ");
            }
            else
            {
                countsql.append(" where  1=1 ");
                sb.append(" where  1=1 ");
            }
            if (!StringUtils.isBlank(pinPai))
            {
                sb.append(" and result.pinPai =:pinPai ");
                countsql.append(" and result.pinPai =:pinPai ");
            }
            if (!StringUtils.isBlank(xingHao))
            {
                sb.append(" and result.xingHao=:xingHao ");
                countsql.append(" and result.xingHao=:xingHao ");
            }
        }
        else
        {
            countsql.append("select count(1) as counNum  from ").append("(select  t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,NULL as imageUrlTrue,t.minImageUrl,t.marketPrice,null as marketPriceTrue,t.ourPrice,null as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,t.sort+1000 as sort,null as province,null as city,null as town  ").append("from t_battery t ").append("UNION ALL ").append(
                    "select  t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,p.imageUrl as imageUrlTrue,t.minImageUrl,t.marketPrice,p.marketPrice as marketPriceTrue,t.ourPrice,p.ourPrice as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,p.sort ,p.province,p.city,p.town  ").append("from t_battery t  join t_battery_price  p ON p.batteryId=t.id   where 1=1  ");
            if (!StringUtils.isBlank(province))
            {
                countsql.append(" and p.province=:province ");
            }
            else
            {
                countsql.append(" and 1=2 ");
            }
            if (!StringUtils.isBlank(city))
            {
                countsql.append(" and p.city=:city ");
            }
            countsql.append(") as result ");

            sb.append("select result.id, result.name, result.zhongliang,result.licheng,result.pinPai,result.xingHao,result.subName,result.imageUrl,result.imageUrlTrue,result.minImageUrl,result.marketPrice,result.marketPriceTrue,result.ourPrice,result.ourPriceTrue,result.detailInfo,result.crateDate,result.updateDate,result.sort,result.province,result.city,result.town   from ").append(
                    "(select  t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,NULL as imageUrlTrue,t.minImageUrl,t.marketPrice,null as marketPriceTrue,t.ourPrice,null as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,t.sort+1000 as sort,null as province,null as city,null as town ").append("from t_battery t ").append("UNION ALL ").append(
                    "select  t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,t.imageUrl,p.imageUrl as imageUrlTrue,t.minImageUrl,t.marketPrice,p.marketPrice as marketPriceTrue,t.ourPrice,p.ourPrice as ourPriceTrue,t.detailInfo,t.crateDate,t.updateDate,p.sort ,p.province,p.city,p.town ").append("from t_battery t  join t_battery_price  p ON p.batteryId=t.id   where 1=1  ");
            if (!StringUtils.isBlank(province))
            {
                sb.append("and  p.province=:province ");
                if (!StringUtils.isBlank(city))
                {
                    sb.append(" and p.city=:city ");
                }
            }
            else
            {
                sb.append("and 1=2 ");
            }
            sb.append(")  result ");

            if (null != cxid)
            {
                countsql.append("join t_Battery_CheXing r on r.dianchiId=result.id ").append(" where r.chexingId=:cxid ");
                sb.append("join t_Battery_CheXing r on r.dianchiId=result.id ").append(" where r.chexingId=:cxid ");
            }
            else
            {
                countsql.append(" where 1=1 ");
                sb.append(" where 1=1 ");
            }
            if (!StringUtils.isBlank(pinPai))
            {
                sb.append(" and result.pinPai =:pinPai ");
                countsql.append(" and result.pinPai =:pinPai ");
            }
            if (!StringUtils.isBlank(xingHao))
            {
                sb.append(" and result.xingHao=:xingHao ");
                countsql.append(" and result.xingHao=:xingHao ");
            }
        }
        if (!StringUtils.isBlank(orderBy))
        {
            sb.append("  order by " + orderBy);
            if (!StringUtils.isBlank(orderType))
            {
                sb.append("  " + orderType);
            }
        }
        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("imageUrlTrue", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("marketPriceTrue", StandardBasicTypes.STRING)
                .addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("ourPriceTrue", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("province", StandardBasicTypes.STRING).addScalar("city", StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(VoBattery.class));

        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
            query.setString("province", province);
            query.setString("city", city);
            if (null != cxid)
            {
                query.setLong("cxid", cxid);
            }
        }
        else
        {
            if (!StringUtils.isBlank(province))
            {
                query.setString("province", province);
                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
            }
            if (null != cxid)
            {
                query.setLong("cxid", cxid);
            }
            if (!StringUtils.isBlank(pinPai))
            {
                query.setString("pinPai", pinPai);
            }
            if (!StringUtils.isBlank(xingHao))
            {
                query.setString("xingHao", xingHao);
            }
        }
        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            query.setFirstResult(from);
            query.setMaxResults(pageSize);
        }
        List<VoBattery> list = query.list();
        query = getCurrentSession().createSQLQuery(countsql.toString()).addScalar("counNum", StandardBasicTypes.INTEGER);
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
            query.setString("province", province);
            query.setString("city", city);
            if (null != cxid)
            {
                query.setLong("cxid", cxid);
            }
        }
        else
        {
            if (!StringUtils.isBlank(province))
            {
                query.setString("province", province);
                if (!StringUtils.isBlank(city))
                {
                    query.setString("city", city);
                }
            }
            if (null != cxid)
            {
                query.setLong("cxid", cxid);
            }
            if (!StringUtils.isBlank(pinPai))
            {
                query.setString("pinPai", pinPai);
            }
            if (!StringUtils.isBlank(xingHao))
            {
                query.setString("xingHao", xingHao);
            }
        }
        if (pageSize > 0)
        {
            int totalCount = (Integer) query.uniqueResult();
            int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
            json.setTotalSize(totalCount);
            json.setTotalPage(totalPage);
            json.setPageSize(pageSize);
            json.setCurrentPage(currentPage);
        }
        if (null != list && list.size() > 0)
        {
            for (VoBattery vo : list)
            {
                if (!StringUtils.isBlank(vo.getImageUrlTrue()))
                {
                    vo.setImageUrl(vo.getImageUrlTrue());
                }
                if (!StringUtils.isBlank(vo.getMarketPriceTrue()))
                {
                    vo.setMarketPrice(vo.getMarketPriceTrue());
                }
                if (!StringUtils.isBlank(vo.getOurPriceTrue()))
                {
                    vo.setOurPrice(vo.getOurPriceTrue());
                }
            }
        }
        json.setResult(list);
        json.setSuccess(true);
        return json;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Battery queryBettyById(Long id , String province , String city , String town)
    {
        Query query = null;
        StringBuffer sb = new StringBuffer();
        sb.append("select  t.id, t.name, t.zhongliang,t.licheng,t.pinPai,t.xingHao,t.subName,p.imageUrl,p.minImageUrl,p.marketPrice,p.ourPrice,t.detailInfo,t.crateDate,t.updateDate, p.sort ").append(" from t_Battery t left join  t_Battery_Price p  on t.id=p.batteryId ").append(" where t.id=:id  ");
        if (!StringUtils.isBlank(town))
        {
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 3)
                {
                    sb.append(" and p.province  like :province");
                }
                else
                {
                    sb.append(" and p.province  like :province");
                }
            }
            if (!StringUtils.isBlank(city))
            {
                sb.append(" and p.city like :city");
            }
            if (town.length() <= 4)
            {
                sb.append(" and p.town like :town");
            }
            else
            {
                sb.append(" and p.town like :town");
            }
        }
        else
        {
            sb.append("and  p.province is null and p.city is null and p.town is null ");
        }
        query = getCurrentSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("name", StandardBasicTypes.STRING).addScalar("zhongliang", StandardBasicTypes.STRING).addScalar("licheng", StandardBasicTypes.STRING).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("subName", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl", StandardBasicTypes.STRING).addScalar("marketPrice",
                StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Battery.class));
        query.setLong("id", id);

        if (!StringUtils.isBlank(town))
        {
            if (town.length() <= 4)
            {
                query.setString("town", town.substring(0, town.length() - 1) + "%");
            }
            else
            {
                query.setString("town", "%" + town.substring(0, town.length() - 1) + "%");
            }
            if (!StringUtils.isBlank(province))
            {
                if (province.length() > 4)
                {
                    query.setString("province", province.substring(0, 2) + "%");
                }
                else
                {
                    query.setString("province", province + "%");
                }
            }
            if (!StringUtils.isBlank(city))
            {
                query.setString("city", city + "%");
            }
        }
        List<Battery> l = query.list();
        if (null != l && l.size() == 1)
        {
            return l.get(0);
        }
        else
        {
            query = getCurrentSession().createQuery("from Battery where id=:id ");
            query.setLong("id", id);
            l = query.list();
            if (null != l && l.size() == 1)
            {
                return l.get(0);
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public HSSFWorkbook exportExcel(String province , String city , String town , String pinPai , String xingHao)
    {
        if (log.isDebugEnabled())
        {
            log.debug("传入参数 province is" + province + " ,city is " + city + ", town is " + town + ",pinPai is " + pinPai + ",xingHao is " + xingHao);
        }
        StringBuilder sql = new StringBuilder();
        Query query = null;
        sql.append("select p.id, b.pinPai, b.xingHao, p.batteryId, b.imageUrl, p.marketPrice,  p.ourPrice,p.sort,p.province,p.city,p.town,p.updateDate from  t_Battery_Price p join t_Battery b on p.batteryId=b.id  where 1=1 ");

        if (!StringUtils.isBlank(province))
        {
            sql.append(" and p.province =:province");
        }
        if (!StringUtils.isBlank(city))
        {
            sql.append(" and p.city =:city");
        }
        if (!StringUtils.isBlank(town))
        {
            sql.append(" and p.town=:town");
        }
        if (!StringUtils.isBlank(pinPai))
        {
            sql.append(" and b.pinPai =:pinPai");
        }
        if (!StringUtils.isBlank(xingHao))
        {
            sql.append(" and b.xingHao=:xingHao");
        }
        query = getCurrentSession().createSQLQuery(sql.toString()).addScalar("id", StandardBasicTypes.LONG).addScalar("pinPai", StandardBasicTypes.STRING).addScalar("xingHao", StandardBasicTypes.STRING).addScalar("batteryId", StandardBasicTypes.LONG).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).addScalar("province", StandardBasicTypes.STRING).addScalar("city",
                StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).setResultTransformer(Transformers.aliasToBean(BatteryPrice.class));

        if (!StringUtils.isBlank(province))
        {
            query.setString("province", province);
        }
        if (!StringUtils.isBlank(city))
        {
            query.setString("city", city);
        }
        if (!StringUtils.isBlank(town))
        {
            query.setString("town", town);
        }
        if (!StringUtils.isBlank(pinPai))
        {
            query.setString("pinPai", pinPai);
        }
        if (!StringUtils.isBlank(xingHao))
        {
            query.setString("xingHao", xingHao);
        }

        query.setMaxResults(BuzConst.MAX_RESULT);
        List<BatteryPrice> list = query.list();
        HSSFWorkbook book = null;
        if (null != list && list.size() > 0)
        {
            // 要从对象中获取的属性名
            String fieldNames[] = { "province", "city", "town", "pinPai", "xingHao", "batteryId", "imageUrl", "marketPrice", "ourPrice", "updateDate","sort"};
            String titleNames[] = { "省", "市", "区（县）", "品牌", "型号", "电池编号", "电池图片链接", "市场价格", "本网价格", "更新日期","排列顺序" };

            try
            {
                book = ExcelUtil.writeToExcelFile(list, fieldNames, titleNames, 0, 0, "filename", "sheetname", true, null, MsOfficeVersion.Office2003, null);
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }

        return book;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> addExcel(String filePath , String errFilePath)
    {
        Map<String, Object> map = new HashMap<String, Object>();
        String[][] data = new String[6][];
        try
        {
            data = ExcelUtil.readFromExcelFile(filePath, 1, 0, 7, "yyyy-MM-dd", "");
            if (null != data && data.length > BuzConst.MAX_RESULT)
            {
                map.put("success", false);
                map.put("message", "最多一次导入" + BuzConst.MAX_RESULT + "条记录");
                return map;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            log.debug("导入电池价格信息出现异常");
            map.put("success", false);
            map.put("message", "导入出现异常");
            return map;
        }
        map.put("success", false);
        map.put("message", "默认无数据导入失败");
        List<VoBatteryPrice> errlist = new ArrayList<VoBatteryPrice>();
        if (data.length > 1)
        {
            String[] tem = data[0];
            if (tem.length < 7)
            {
                map.put("message", "请下载模板，按照模板填写要要导入内容");
                return map;
            }
            String province = tem[0];
            String city = tem[1];
            String town = tem[2];
            String pinpai = tem[3];
            String xinghao = tem[4];
            String marketPrice = tem[5];
            String ourPrice = tem[6];
            String sort = tem[7];
            int intSort = 1;

            if (StringUtils.isBlank(province) || StringUtils.isBlank(city) || StringUtils.isBlank(town) || StringUtils.isBlank(pinpai) || StringUtils.isBlank(xinghao) || StringUtils.isBlank(ourPrice) || StringUtils.isBlank(sort))
            {
                map.put("message", "请按照模板填写要要导入内容");
                return map;
            }
            else if ((province.contains("省")) && (city.contains("市")) && (town.contains("县")) && (pinpai.contains("品牌")) && (xinghao.contains("型号")) && (marketPrice.contains("市场价")) && (ourPrice.contains("价")) && (sort.contains("序")))
            {
                Session session = getCurrentSession();
                Query query = null;
                String existSql = "select p.id,p.batteryId,p.province,p.city,p.town,p.crateDate,p.detailInfo,p.imageUrl,p.marketPrice,p.ourPrice,p.sort,p.updateDate,p.minImageUrl from  t_Battery_Price p  join t_Battery t   on t.id=p.batteryId  where p.province=:province and p.city=:city and p.town=:town and t.pinPai=:pinPai and t.xingHao=:xingHao ";

                String pctCheckHql = " from District where name=:name and pname=:pname ";

//                String pxCheckHql = " from Dict where title=:title and  value=:value";
                
                String bCheckHql = " from Battery where pinPai=:pinPai and  xingHao=:xingHao";
                

                for (int i = 1; i < data.length && i <= BuzConst.MAX_RESULT; i++)//最大支持Const.MAX_RESULT个批量导入
                {
                    tem = data[i];
                    province = tem[0];
                    city = tem[1];
                    town = tem[2];
                    pinpai = tem[3];
                    xinghao = tem[4];
                    marketPrice = tem[5];
                    ourPrice = tem[6];
                    sort = tem[7];

                    VoBatteryPrice p = new VoBatteryPrice();
                    p.setProvince(province);
                    p.setPinPai(pinpai);
                    p.setCity(city);
                    p.setTown(town);
                    p.setPinPai(pinpai);
                    p.setXingHao(xinghao);
                    p.setMarketPrice(marketPrice);
                    p.setOurPrice(ourPrice);
                    if (StringUtils.isBlank(province) && StringUtils.isBlank(city) && StringUtils.isBlank(town) && StringUtils.isBlank(pinpai) && StringUtils.isBlank(xinghao) && StringUtils.isBlank(ourPrice))
                    {
                        //第一步校验非空
                        break;
                    }
                    if (StringUtils.isBlank(province) || StringUtils.isBlank(city) || StringUtils.isBlank(town) || StringUtils.isBlank(pinpai) || StringUtils.isBlank(xinghao) || StringUtils.isBlank(ourPrice))
                    {
                        //第一步校验非空
                        p.setErrorMsg("需要填写的数据不可以有空,请检查");
                        errlist.add(p);
                        continue;
                    }

                    if (!StringUtils.isBlank(sort))
                    {
                        try{
                        intSort = Integer.parseInt(sort);}
                        catch (Exception e) {
                            log.debug("ERROR:勉强让你继续导入 排序sort"+sort);
                            intSort=1;
                        }
                    }
                    //第2步校验价格合法
                    Pattern pattern1 = Pattern.compile("^(0|[1-9][0-9]{0,5})(\\.\\d{1,2})?$");
                    Matcher matcher1 = pattern1.matcher(ourPrice);
                    if (!matcher1.matches())
                    {
                        p.setErrorMsg("本网价格不合法");
                        errlist.add(p);
                        continue;
                    }
                    if (!StringUtils.isBlank(marketPrice))
                    {
                        Matcher matcher2 = pattern1.matcher(ourPrice);
                        if (!matcher2.matches())
                        {
                            p.setErrorMsg("市场价格不合法");
                            errlist.add(p);
                            continue;
                        }
                    }
                    //检测该地区价格是否已经设置了
                    query = session.createSQLQuery(existSql).addScalar("id", StandardBasicTypes.LONG).addScalar("batteryId", StandardBasicTypes.LONG).addScalar("province", StandardBasicTypes.STRING).addScalar("city", StandardBasicTypes.STRING).addScalar("town", StandardBasicTypes.STRING).addScalar("crateDate", StandardBasicTypes.STRING).addScalar("updateDate", StandardBasicTypes.STRING).addScalar("detailInfo", StandardBasicTypes.STRING).addScalar("imageUrl", StandardBasicTypes.STRING).addScalar("minImageUrl",
                            StandardBasicTypes.STRING).addScalar("marketPrice", StandardBasicTypes.STRING).addScalar("ourPrice", StandardBasicTypes.STRING).addScalar("sort", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(BatteryPrice.class));
                    query.setString("province", province);
                    query.setString("city", city);
                    query.setString("town", town);
                    query.setString("pinPai", pinpai);
                    query.setString("xingHao", xinghao);
                    List<BatteryPrice> l1 = query.list();
                    if (null != l1 && l1.size() > 0)
                    {
                        //已经存在更新价格
                        BatteryPrice price = l1.get(0);
                        price.setOurPrice(ourPrice);
                        price.setUpdateDate(DateUtils.getCurrDate(null));
                        session.update(price);
                        continue;
                    }
                    else
                    {
                        query = session.createQuery(pctCheckHql);
                        query.setString("pname", province);
                        query.setString("name", city);
                        List c1 = query.list();
                        if (null == c1 || 0 == c1.size())
                        {
                            p.setErrorMsg("本网不存在该地址，请检查省市");
                            errlist.add(p);
                            continue;
                        }
                        query.setString("pname", city);
                        query.setString("name", town);
                        c1 = query.list();
                        if (null == c1 || 0 == c1.size())
                        {
                            p.setErrorMsg("本网不存在该地址，请检查市和区县");
                            errlist.add(p);
                            continue;
                        }
                        
                        //检测品牌型号
                        query = session.createQuery(bCheckHql);
                        query.setString("pinPai", pinpai);
                        query.setString("xingHao", xinghao);
                        
                        List<Battery> bl=query.list();
                        if(null!=bl&&bl.size()>0){
                            Battery b=bl.get(0);
                            //全都通过了保存
                            BatteryPrice m = new BatteryPrice();
                            m.setProvince(province);
                            m.setCity(city);
                            m.setTown(town);
                            m.setPinPai(pinpai);
                            m.setXingHao(xinghao);
                            m.setMarketPrice(marketPrice);
                            m.setOurPrice(ourPrice);
                            m.setCrateDate(DateUtils.getCurrDate(null));
                            m.setSort(intSort);
                            m.setBatteryId(b.getId());
                            session.save(m);
                            
                        }else{
                            p.setErrorMsg("本网不存在该品牌型号电池");
                            errlist.add(p);
                            continue;
                        }
                    }
                }
                map.put("success", true);
                map.put("message", "导入成功");
                if (errlist.size() > 0)
                {
                    map.put("message", "有数据不合法");
                    // 要从对象中获取的属性名
                    String fieldNames[] = { "province", "city", "town", "pinPai", "xingHao", "marketPrice", "ourPrice", "errorMsg" };
                    String titleNames[] = { "省", "市", "区（县）", "品牌", "型号", "市场价格", "本网价格", "错误信息" };
                    try
                    {
                        String fileName = System.currentTimeMillis() + "";
                        fileName = ExcelUtil.writeToExcelFileTemp(errlist, fieldNames, titleNames, 0, 0, errFilePath + "\\" + fileName, "sheetname", true, null, MsOfficeVersion.Office2003, null);
                        map.put("errorFile", fileName);
                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }
            }
            else
            {
                map.put("success", false);
                map.put("message", "请下载模板，按照模板填写要要导入内容");
                return map;
            }
        }
        else
        {
            map.put("success", false);
            map.put("message", "无数据导入,请按模板填写导入");
        }
        return map;
    }
}